/*
 * Copyright 2007. Mount Sinai Hospital, Toronto, Canada.
 * 
 * Licensed under the GNU Lesser General Public License, Version 2. You
 * can find a copy of the license at:
 * 
 * http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt
 * 
 * IN NO EVENT SHALL MOUNT SINAI HOSPITAL BE LIABLE TO ANY PARTY FOR DIRECT, 
 * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST 
 * PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, 
 * EVEN IF MOUNT SINAI HOSPITAL HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH 
 * DAMAGE.
 * 
 * MOUNT SINAI HOSPITAL SPECIFICALLY DISCLAIMS ANY IMPLIED WARRANTIES OF 
 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE AND 
 * ACCOMPANYING DOCUMENTATION, IF ANY, PROVIDED HEREUNDER IS PROVIDED "AS IS". 
 * MOUNT SINAI HOSPITAL HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, 
 * UPDATES, ENHANCEMENTS, OR MODIFICATIONS. 
 */
package com.sinai.mshab.server;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.sinai.mshab.client.exception.SerializableFileNotFoundException;
import com.sinai.mshab.client.exception.SerializableSQLException;
import com.sinai.mshab.client.services.Service;
import com.sinai.mshab.client.user.LDAPUser;
import com.sinai.mshab.client.util.Answer;
import com.sinai.mshab.client.util.AnswerCollection;
import com.sinai.mshab.client.util.FieldName;
import com.sinai.mshab.client.util.WhereClause;
import com.sinai.mshab.client.ui.Key;
import com.sinai.mshab.server.sql.DbFilter;
import com.sinai.mshab.server.sql.DbFilterGroup;
import com.sinai.mshab.server.sql.SQLDelete;
import com.sinai.mshab.server.sql.SQLInsert;
import com.sinai.mshab.server.sql.SQLSelect;
import com.sinai.mshab.server.sql.SQLUpdate;

/**
 * This is the server-side implementation of the methods defined in the Services
 * class.
 * 
 * Essentially the purpose of this class is to communicate with the database, to
 * either retreive or store data depending on the requeset from the client side.
 * 
 * @author serge
 * 
 */
public class Server extends RemoteServiceServlet implements Service {

	/**
	 * The connection used to the database.
	 */
	protected static Connection conn;

	/**
	 * Boolean to keep track of whether any updates took place.
	 */
	protected boolean had_an_update;

	/**
	 * boolean to keep track if the server has been initialized or not
	 */
	protected boolean initialized = false;

	/**
	 * This arraylist will contain strings of table names that never should be
	 * inserted into.
	 */
	protected ArrayList neverInsertTables = new ArrayList();

	/**
	 * This arraylist will contain strings of table names that never should be
	 * updated with sql UPDATE command.
	 */
	protected ArrayList neverUpdateTables = new ArrayList();

	/**
	 * User defined server set up.
	 */
	public void initialize() {
		System.out.println("DEFAULT SERVER INIT.");
		initialized = true;
	}

	/**
	 * Establishes a connection with the database. Might be a good idea to over
	 * ride this method in a specific sever class.
	 */
	public void getConnection() {

	}

	/**
	 * Establishes a connection with the database using the provided
	 * information.
	 * 
	 * @param connectionString
	 *            the URL to the database.
	 * @param dbName
	 *            the database's name.
	 * @param driver
	 *            the driver to use.
	 * @param userName
	 *            the username that has permissions on the database.
	 * @param password
	 *            the password of the username.
	 */
	public void getConnection(String connectionString, String dbName,
			String driver, String userName, String password) {

		initialize();

		conn = null;
		try {
			Class.forName(driver).newInstance();
			conn = DriverManager.getConnection(connectionString + dbName,
					userName, password);
			System.out.println("Connected to the database");
		} catch (Exception e) {
			System.out.println("Connecting to the DB failed.");
			System.out.println(e.toString());
		}
	}

	/**
	 * Closes the connection to the database.
	 */
	public void closeConnection() {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * Returns the connection to the database created by the server.
	 * 
	 * @return the connection to the database created by the server.
	 */
	public static Connection getConn() {
		return conn;
	}

	/**
	 * Retrieves the tables from the database.
	 * 
	 * @return an arraylist containing strings with the names of the available
	 *         tables in the database.
	 */
	public ArrayList getTables() {

		System.out.println("Server sending out table names");

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		ArrayList table_names = new ArrayList();

		try {
			// going through all the tables in the database.
			ResultSet tables = conn.createStatement().executeQuery(
					"show tables");
			while (tables.next()) {
				table_names.add(tables.getString(1));
			}
		} catch (Exception e) {
		}
		return table_names;
	}

	/**
	 * Retrieves the tables from the database.
	 * 
	 * @return an arraylist containing strings with the names of the available
	 *         tables in the database.
	 * 
	 * @gwt.typeArgs <java.lang.String, java.util.ArrayList>
	 */
	public HashMap getColumnNames() {

		System.out.println("Server sending out column names");

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		HashMap columns = new HashMap();

		try {
			// going through all the tables in the database.
			ResultSet tables = conn.createStatement().executeQuery(
					"show tables");
			while (tables.next()) {
				String table_name = tables.getString(1);

				ResultSet col_rs = conn.createStatement().executeQuery(
						"show columns from " + table_name);

				columns.put(table_name, new ArrayList());

				// adding all the columns to the current table name.
				while (col_rs.next()) {
					String column_name = col_rs.getString(1);
					String field_type = col_rs.getString(2);
					ArrayList list = (ArrayList) columns.get(table_name);
					FieldName field = new FieldName();
					field.setName(column_name);
					field.setTable(table_name);
					field.setType(field_type);
					list.add(field);
				}

			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return columns;
	}

	/**
	 * Checks that the key exists in the database.
	 * 
	 * @param k
	 *            the key to be checked
	 * @return true if key exists, false otherwise.
	 */
	public boolean validKey(Key k) {

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		try {
			// construct the sql check
			String sql = "SELECT * FROM " + k.getTableName() + " WHERE ";

			Iterator it = k.getKeys().keySet().iterator();
			int count = 0;
			while (it.hasNext()) {
				String column = (String) it.next();
				String val = (String) k.getKeys().get(column);

				if (count == 0) {
					sql += " " + column + "='" + val + "' ";
				} else {
					sql += " AND " + column + "='" + val + "' ";
				}
				count++;
			}

			// going through all the tables in the database.
			System.out.println("VALIDITY CHECK OF: " + sql);
			ResultSet success = conn.createStatement().executeQuery(sql);

			if (success.next()) {
				return true;
			}
		} catch (Exception e) {

		}

		return false;
	}

	/**
	 * Executes a custom sql statement. Returns a 2d array, represented by
	 * arraylist of arraylists Does not return everything, but a range. very
	 * useful for populating Table.
	 * 
	 * @param sql
	 *            the sql statement to execute.
	 * @param start
	 *            the first record to return
	 * @param end
	 *            the last record to return
	 * @return arraylist of arraylists, a 2d array with the information the
	 *         first arraylist contains an integer with the total record count.
	 */
	public ArrayList execteSQL(String sql, int start, int end) {

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		ArrayList result = new ArrayList();
		int current_record_number = 0;

		try {
			// going through all the tables in the database.
			ResultSet data = conn.createStatement().executeQuery(sql);
			ResultSetMetaData meta = data.getMetaData();

			ArrayList answer = new ArrayList();
			for (int i = 1; i <= meta.getColumnCount(); i++) {
				answer.add(meta.getColumnName(i));
			}
			result.add(answer);

			// walking through the result.
			while (data.next()) {

				// update record count.
				current_record_number++;

				// Only store the requested records, skiping past uneeded
				// records
				if (current_record_number >= start
						&& current_record_number < end) {
					answer = new ArrayList();

					for (int i = 1; i <= meta.getColumnCount(); i++) {
						answer.add(data.getString(i));
					}
					result.add(answer);
				}
			}
		} catch (Exception e) {
			System.out.println("FAILED TO EXECUTE " + sql);
		}

		// appending the total number of records.
		result.add(0, new Integer(current_record_number));

		return result;
	}

	/**
	 * Executes the provided insert or update statement. First executes the
	 * update, if no rows were affected executes the insert sql instead.
	 * 
	 * @param sqlStatment
	 *            the sql statement to update informtaion
	 */
	protected int executeSQL(PreparedStatement sqlStatment) {

		int rows_affected = 0;

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		if (sqlStatment == null) {
			return rows_affected;
		}

		// attempting to execute the sql
		try {
			rows_affected = sqlStatment.executeUpdate();

			if (rows_affected > 0) {
				had_an_update = true;
				System.out.println("SUCCESSFULLY EXECUTED: " + sqlStatment);
			}

			// close the statement after we finish execution.
			sqlStatment.close();
		} catch (Exception e) {
			System.out.println("FAILED TO EXECUTE: " + sqlStatment);
			System.out.println(e.toString());
		}

		return rows_affected;
	}

	/**
	 * Method takes in an array of answer collections and a key. Places the data
	 * stored in the answer collections with the given primary key to the
	 * database.
	 * 
	 * @param a
	 *            the list of answer collections
	 * @param dbEntryKey
	 *            the primary key associated with the data.
	 * @throws SerializableSQLException
	 * @throws SQLException
	 */
	public void sendAnswer(AnswerCollection a, Key dbEntryKey)
			throws SerializableSQLException {
		AnswerCollection current_collection;
		Answer current_answer;
		String table_name = "";

		SQLUpdate update;
		SQLInsert insert;
		SQLDelete delete;

		had_an_update = false;

		current_collection = a;

		// going throgh answers of collection
		for (int i = 0; i < current_collection.size(); i++) {
			current_answer = current_collection.get(i);

			// setting the table name
			table_name = current_answer.getTableName();

			// if the table name is null, move on to next answer
			if (table_name == null || table_name.trim().length() == 0) {
				continue;
			}

			// create the update and insert objects.
			update = new SQLUpdate(getConn(), table_name);
			insert = new SQLInsert(getConn(), table_name);

			// prepare the delete statement if applicable.
			delete = prepareDelete(current_answer, table_name);

			HashMap fields_map = current_answer.getHashMap();
			Iterator keys = fields_map.keySet().iterator();

			// if the collection is non-mergable, handle this collection
			// sperately
			WhereClause w;

			// if we're dealing with a non-mergable Answer object, we want to
			// store that objects "id" values.
			if (!current_answer.isMergeable()) {
				ArrayList wheres = current_answer.getWhereClauses();

				// going through the where clauses
				for (int k = 0; k < wheres.size(); k++) {
					w = (WhereClause) wheres.get(k);

					// add the information of the WhereClause to the SQLObject
					// and assume the operator is equals.
					update.addWhere(w.getColumn(), w.getValue(), "=");
					insert.add(w.getColumn(), w.getValue());
				}
			}

			// going through the keys of the answer
			while (keys.hasNext()) {
				String field = (String) keys.next();
				String value = (String) fields_map.get(field);

				// building up the columns and values
				if (!field.startsWith("ignore")
						&& !dbEntryKey.getKeys().containsKey(field)
						&& value.length() > 0) {
					update.add(field, value);
					insert.add(field, value);
				}
			}

			// go through the dbEntryKey and add it's value to every SQL object.
			Iterator dbKeyIter = dbEntryKey.getKeys().keySet().iterator();
			while (dbKeyIter.hasNext()) {

				// get the key and it's associated value.
				String field = (String) dbKeyIter.next();
				String value = (String) dbEntryKey.getKeys().get(field);

				// add this information to the update, insert and delete (if
				// applicable). We assume equals.
				update.addWhere(field, value, "=");
				insert.add(field, value);
				if (delete != null) {
					delete.addWhere(field, value, "=");
				}
			}

			try {

				// if there is anything to delete, that means that we are
				// definately not updating or inserting. Therefore, we execute
				// the delete and finish.
				if (delete != null) {
					System.out.println("Attempting to execute: "
							+ delete.getPreparedStatement().toString());
					delete.execute();
					System.out.println("Done");
				} else {

					int rowsAffected = 0;

					// try to update (if allowed).
					if (!neverUpdateTables.contains(table_name)) {
						System.out.println("Attempting to execute: "
								+ update.getPreparedStatement().toString());
						rowsAffected = update.execute();
						System.out.println("Done");
					}

					// if the update didn't work, that means we should do an
					// insertion.
					if (!neverInsertTables.contains(table_name)) {

						if (rowsAffected == 0) {
							System.out.println("Attempting to execute: "
									+ insert.getPreparedStatement().toString());
							insert.execute();
							System.out.println("Done");
						}
					}
				}
			} catch (SQLException e) {
				SerializableSQLException se = new SerializableSQLException();
				se.setStackTrace(e.getStackTrace());
				throw se;
			}
		}
	}

	/**
	 * Retrieves all information from the DB that matches the given key.
	 * 
	 * @param key
	 *            the primary key present in the tables to be used
	 * @return the answer collection list, one answer collection per table.
	 */
	public HashMap retrieveData(Key dbEntryKey) {
		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		HashMap map = new HashMap();
		AnswerCollection ac;
		Answer answer;

		try {
			Statement stm_tables = conn.createStatement();
			Statement tables_select = conn.createStatement();
			String current_table;
			String where = dbEntryKey.toString();

			// going through all the tables in the database.
			ResultSet tables = stm_tables.executeQuery("show tables");
			while (tables.next()) {
				current_table = tables.getString(1);

				String sql_no_id = "SELECT * FROM " + current_table + where;
				String sql = "SELECT * FROM " + current_table + where
						+ " ORDER BY id";

				// check if table contains an ID column
				try {
					tables_select.executeQuery(sql);
				} catch (Exception e) {
					// we got an exception, id column is missing
					sql = sql_no_id;
				}

				/*----------------------------------------------*/
				System.out.println("------------" + current_table
						+ "------------");
				/*----------------------------------------------*/

				// going through the information of current table.
				ac = new AnswerCollection();
				ResultSet data = tables_select.executeQuery(sql);
				while (data.next()) {
					answer = new Answer();

					// going through the columns and adding col-val to the
					// answer.
					ResultSetMetaData meta = data.getMetaData();
					for (int i = 1; i <= meta.getColumnCount(); i++) {

						// if we're dealing with a blob, we don't want to treat
						// it like other data (storing it as a String). We want
						// to parse the blob and get its name.
						int columnType = meta.getColumnType(i);
						if (!(columnType == Types.BINARY
								|| columnType == Types.VARBINARY
								|| columnType == Types.LONGVARBINARY || columnType == Types.BLOB)) {
							answer.add(current_table, meta.getColumnName(i),
									data.getString(i));

							/*----------------------------------------------*/
							System.out.println(meta.getColumnName(i) + " = "
									+ data.getString(i));
							/*----------------------------------------------*/
						} else {

						}
					}

					// adding the anser to the answer collection
					ac.add(answer);
				}

				// adding the answer collection to hashmap
				map.put(current_table, ac);
			}
		} catch (Exception e) {
			System.out.println(e.toString());
		}

		return map;
	}

	/**
	 * Given the username, resets the password and sends an email to the address
	 * associated with the username
	 * 
	 * @param username
	 *            the username of the person who's password is to be reset.
	 */
	public void resetPassword(String username) {

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		try {
			Statement stm = conn.createStatement();
			ResultSet rs = stm
					.executeQuery("SELECT * FROM users WHERE username='"
							+ username + "'");

			// if this user exists.
			if (rs.next()) {
				String email = rs.getString(3);
				String new_pass = "";
				for (int i = 0; i < 6; i++) {
					new_pass += (char) (Math.random() * 25 + 97);
				}

				stm.executeUpdate("UPDATE users SET password='" + new_pass
						+ "' WHERE username='" + username + "'");
				new PasswordResetEmail(email, username, new_pass);
			}
		} catch (Exception e) {

		}
	}

	/**
	 * Returns true if the provided username and password are in the database
	 * 
	 * @param user
	 *            the user id
	 * @param pass
	 *            the user password.
	 * @return true if the username and password exist in the database, false
	 *         otherwise.
	 */
	public LDAPUser validateLogin(String user, String pass) {
		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		try {
			Statement valid_stm = conn.createStatement();
			ResultSet rs = valid_stm
					.executeQuery("SELECT * FROM users WHERE username='" + user
							+ "' AND password='" + pass + "'");
			if (rs.next()) {
				String permission = rs.getString(6);
				LDAPUser user_obj = new LDAPUser();
				user_obj.setUsername(user);
				user_obj.setPermission(permission);
				System.out.println("SERVER setting users permission to"
						+ permission);
				return user_obj;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	/**
	 * This will look up possible values depending on what is typed in so far.
	 * 
	 * @gwt.typeArgs <java.lang.String>
	 */
	public ArrayList getSuggestions(String fieldName, String tableName,
			String so_far) {

		// check if the connection is active
		if (conn == null) {
			getConnection();
		}

		ArrayList results = new ArrayList();
		String sql = "SELECT DISTINCT " + fieldName + " FROM " + tableName
				+ " WHERE " + fieldName + " LIKE '%" + so_far
				+ "%' LIMIT 0 , 30";

		System.out.println("SUGEST SQL: " + sql);

		try {
			Statement stm = conn.createStatement();
			ResultSet rs = stm.executeQuery(sql);

			while (rs.next()) {
				results.add(rs.getString(1));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return results;
	}

	/**
	 * Retreives the permission for a given user.
	 */
	public String getUserPermission(String username) {
		if (conn == null) {
			getConnection();
		}

		SQLSelect userPermission = new SQLSelect(conn);
		userPermission.addSelectField("users", "permission");
		userPermission.addWhere("users", "username", username, "=");
		try {
			ResultSet rs = userPermission.execute();

			while (rs.next()) {
				String permission = rs.getString(1);
				userPermission.close();
				return permission;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	public boolean authenticate(String username, String password) {
		return ServerLDAPAuthentication.authenticate(username, password);
	}

	/**
	 * Stores a file into the database using the <code>filePath</code>.
	 * Returns true if the insertion/update statement executed correctly and
	 * false otherwise.
	 * 
	 * @param fieldName
	 *            the database field name where to store the file. This must be
	 *            a blob field.
	 * @param tableName
	 *            the name of the table where fieldName is.
	 * @param dbKey
	 *            the key associated with the file.
	 * @param filePath
	 *            the path <b>on the server</b> where the file lives.
	 * @return true if the insertion/update statement executed correctly and
	 *         false otherwise.
	 * @throws SerializableFileNotFoundException
	 * @throws SerializableSQLException
	 * @throws FileNotFoundException
	 * @throws SQLException
	 */
	public boolean storeFile(String fieldName, String tableName, Key dbKey,
			String filePath) throws SerializableFileNotFoundException,
			SerializableSQLException {

		boolean result = false;

		// create our SQL objects.
		SQLUpdate update = new SQLUpdate(getConn(), tableName);
		SQLInsert insert = new SQLInsert(getConn(), tableName);

		// add the field name to our SQL object.
		update.add(fieldName, "?");
		insert.add(fieldName, "?");

		// got through the keys and add them.
		Iterator keyIter = dbKey.getKeys().keySet().iterator();
		while (keyIter.hasNext()) {

			// grab the values
			String field = (String) keyIter.next();
			String value = (String) dbKey.getKeys().get(field);

			// add them to the update and insert objects. Assume an equal
			// operator.
			update.addWhere(field, value, "=");
			insert.add(field, value);
		}

		filePath = filePath.trim();

		File file;
		FileInputStream fileIS;

		// set the file section of the PreparedStatement to a FileInputStream so
		// we can store it as a blob.
		file = new File(filePath);
		try {
			fileIS = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			SerializableFileNotFoundException se = new SerializableFileNotFoundException();
			se.setStackTrace(e.getStackTrace());
			throw se;
		}

		PreparedStatement updateStatement = null;
		PreparedStatement insertStatement = null;
		try {
			updateStatement = update.getPreparedStatement();
			insertStatement = insert.getPreparedStatement();

			// we say 1 because the that is the location of the file stream.
			updateStatement.setBinaryStream(1, fileIS, (int) (file.length()));
			insertStatement.setBinaryStream(1, fileIS, (int) (file.length()));
		} catch (SQLException e) {
			SerializableSQLException se = new SerializableSQLException();
			se.setStackTrace(e.getStackTrace());
			throw se;
		}

		int rowsEffected = executeSQL(updateStatement);
		if (!(rowsEffected > 0)) {
			executeSQL(insertStatement);
		}

		return result;
	}

	/**
	 * Returns a {@link SQLDelete} object that contains all the information held
	 * in answer.
	 * 
	 * @param answer
	 *            the {@link Answer} object containing information of what rows
	 *            need to be deleted.
	 * @param tableName
	 *            the name of the table who's rows need deleting.
	 * @return a {@link SQLDelete} object that contains all the information held
	 *         in answer.
	 */
	private SQLDelete prepareDelete(Answer answer, String tableName) {
		SQLDelete delete = null;

		// if we have things to delete, make a new SQLDelete object and add
		// those things to it.
		if (answer.getDeleteKeys().size() > 0) {
			ArrayList deleteKeys = answer.getDeleteKeys();
			delete = new SQLDelete(getConn(), tableName);

			// iterate through keys of rows that need deleting and add them to a
			// DbFilterGroup with OR as the operator.
			DbFilterGroup groupOfKeys = new DbFilterGroup("OR");
			for (int j = 0; j < deleteKeys.size(); j++) {
				Key toDeleteKey = (Key) deleteKeys.get(j);

				// go through every Key in this key.
				Iterator keys = toDeleteKey.getKeys().keySet().iterator();
				while (keys.hasNext()) {
					String field = (String) keys.next();
					String value = (String) toDeleteKey.getKeys().get(field);

					// create a new filter and add it to the group.
					DbFilter keyFilter = new DbFilter(toDeleteKey
							.getTableName(), field, value, "=");
					groupOfKeys.add(keyFilter);
				}
			}

			// now add this group into this SQLDelete's default group.
			delete.addWhere(groupOfKeys);
		}

		return delete;
	}
}